﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web.UI.WebControls;

using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;

namespace dcwj
{
    public partial class PaperList : System.Web.UI.Page
    {
        private String M_CONSTR = ConfigurationManager.ConnectionStrings["dcwjConnectionString"].ToString();
       
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {

                bind();

                if (Request["class"] != null)
                {
                    int n = Int32.Parse(Request["class"].ToString());
                    rdoPaperType.SelectedIndex = n;
                    if (n == 1)
                    {
                        btnCompareTeacher.Visible = false;
                    }
                    else if (n == 2)
                    {
                        btnCompareClass.Visible = false;
                        btnCompareTAll.Visible = false;
                        rdoPaperType.Visible = false;
                        ddlTeacher.Visible = false;
                        lblPType.Visible = false;
                        lblTeacherNM.Visible = false;
                    }
                    rdoPaperType.Enabled = false;
                }
                else
                {
                    btnCompareClass.Visible = false;
                    btnCompareTAll.Visible = false;
                    btnCompareTeacher.Visible = false;
                }

                QueryPaper();

                btnQuery.Attributes.Add("onclick", "return checkDate();");
                btnCompareClass.Attributes.Add("onclick", "return checkSel();");
                btnCompareTeacher.Attributes.Add("onclick", "return checkSel();");
                btnCompareTAll.Attributes.Add("onclick", "return checkSel();");
            }
        }

        private void bind()
        {
            Database objDB = new SqlDatabase(M_CONSTR);
            if (objDB != null)
            {
                //品牌
                DataSet ds = objDB.ExecuteDataSet(CommandType.Text, "SELECT * FROM Table_BRAND");
                ddlBrand.DataSource = ds.Tables[0];
                ddlBrand.DataTextField = "BrandName";
                ddlBrand.DataValueField = "BrandID";
                ddlBrand.DataBind();
                ddlBrand.Items.Insert(0,(new ListItem("--全部--", "0")));
                ddlBrand.SelectedIndex = 0;

                //岗位
                ds = objDB.ExecuteDataSet(CommandType.Text, "SELECT * FROM Table_POSITION");
                ddlPosition.DataSource = ds.Tables[0];
                ddlPosition.DataTextField = "PositionName";
                ddlPosition.DataValueField = "PositionID";
                ddlPosition.DataBind();
                ddlPosition.Items.Insert(0, (new ListItem("--全部--", "0")));
                ddlPosition.SelectedIndex = 0;

                //区域
                ds = objDB.ExecuteDataSet(CommandType.Text, "SELECT * FROM Table_REGION");
                ddlRegion.DataSource = ds.Tables[0];
                ddlRegion.DataTextField = "RegionName";
                ddlRegion.DataValueField = "RegionID";
                ddlRegion.DataBind();
                ddlRegion.Items.Insert(0, (new ListItem("--全部--", "0")));
                ddlRegion.SelectedIndex = 0;

                //所属部门
                ds = objDB.ExecuteDataSet(CommandType.Text, "SELECT * FROM Table_Group");
                ddlTGroup.DataSource = ds.Tables[0];
                ddlTGroup.DataTextField = "GroupName";
                ddlTGroup.DataValueField = "GroupID";
                ddlTGroup.DataBind();
                ddlTGroup.Items.Insert(0, (new ListItem("--全部--", "0")));
                ddlTGroup.SelectedIndex = 0;

                //讲师
                ds = objDB.ExecuteDataSet(CommandType.Text, "SELECT * FROM Table_TEACHER");
                ddlTeacher.DataSource = ds.Tables[0];
                ddlTeacher.DataTextField = "TeacherName";
                ddlTeacher.DataValueField = "TeacherID";
                ddlTeacher.DataBind();
                ddlTeacher.Items.Insert(0, (new ListItem("--全部--", "0")));
                ddlTeacher.SelectedIndex = 0;

                //问卷类型
                ds = objDB.ExecuteDataSet(CommandType.Text, "SELECT * FROM Table_PAPERCLASS");
                rdoPaperType.DataSource = ds.Tables[0];
                rdoPaperType.DataTextField = "PaperClassName";
                rdoPaperType.DataValueField = "PaperClassiD";
                rdoPaperType.DataBind();
                rdoPaperType.Items.Insert(0,new ListItem("全部", "0"));
                rdoPaperType.SelectedIndex = 0;
            }
        }

        private void QueryPaper()
        {
            Database objDB = new SqlDatabase(M_CONSTR);
            if (objDB != null)
            {
                string strSql = string.Format(@"SELECT 
                                              PaperID,
                                              PaperName,
                                              TeacherID,
                                              (select BrandName from Table_BRAND where BrandID = p.BrandID) as BrandName,
                                              (select RegionName from Table_REGION where RegionID = p.RegionID) as RegionName,
                                              (select PositionName from Table_POSITION where PositionID = p.PositionID) as PositionName,
                                              (select TeacherName from Table_TEACHER where TeacherID = p.TeacherID) as TeacherName,
                                              (select PaperClassName from Table_PAPERCLASS where PaperClassID = p.PaperClassID) as PaperClassName,
                                              CONVERT(varchar(100), StartTime, 23) as StartTime,
                                              CONVERT(varchar(100), EndTime, 23) as EndTime,
                                              SubmitTimes
                                              FROM Table_PAPER p
                                              where 
                                              (p.BrandID = {0}  or {0} = 0) and
                                              (p.RegionID = {1} or {1} = 0) and
                                              (p.PositionID = {2} or {2} = 0) and
                                              (p.TeacherID = {3} or {3} = 0) and
                                              (p.StartTime>='{4}' or '{4}'='') and
                                              (p.EndTime<='{5}' or '{5}'='') and
                                              (p.PaperName like '%{6}%' or '{6}'='') and
                                              (p.PaperClassID ={7} or {7} = 0) and
                                              (p.TeacherID in (select TeacherID from Table_TEACHER where GroupID ={8} or {8} = 0))
                                              order by CreateTime desc",
                                              ddlBrand.SelectedValue,
                                              ddlRegion.SelectedValue,
                                              ddlPosition.SelectedValue,
                                              ddlTeacher.SelectedValue,
                                              dtStart.Value,
                                              dtEnd.Value,
                                              txtPaperNm.Text.Trim(),
                                              rdoPaperType.SelectedValue,
                                              ddlTGroup.SelectedValue);
                DataSet ds = objDB.ExecuteDataSet(CommandType.Text, strSql);
                GridView1.DataSource = ds.Tables[0];
                GridView1.DataBind();
                GridView1.Columns[1].Visible = false;

                if (rdoPaperType.SelectedIndex != 1)//只有完整版才有，单班报告、导出数据
                {
                    GridView1.Columns[3].Visible = false;
                    if (rdoPaperType.SelectedIndex != 2)//讲师版也有导出数据
                    {
                        //GridView1.Columns[4].Visible = false;
                    }
                }
                
                lblCount.Text = "每页 10 项，当前共 " + ds.Tables[0].Rows.Count + " 项";
            }
        }

        protected void btnQuery_Click(object sender, EventArgs e)
        {
            QueryPaper();
        }

    
        protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            int n = 0;
            Database objDB = new SqlDatabase(M_CONSTR);
            string strID =  GridView1.DataKeys[e.RowIndex].Value.ToString();//strID就是该行的ID 
            string strSQL = "Delete from Table_PAPER " + " WHERE PaperID = " + strID;
            n += objDB.ExecuteNonQuery(CommandType.Text, strSQL);
            strSQL = "Delete from Table_RESULT " + " WHERE PaperID = " + strID;
            n += objDB.ExecuteNonQuery(CommandType.Text, strSQL);
            if (n > 0)
            {
                ClientScript.RegisterStartupScript(GetType(), "Message", "<SCRIPT LANGUAGE='javascript'>alert('删除成功!');</script>");
            }
            QueryPaper();
        }

        protected void btnCompareClass_Click(object sender, EventArgs e)
        {
            getSelectedItemNjump("~/report/bcdbPage.aspx");
        }

        protected void btnCompareTeacher_Click(object sender, EventArgs e)
        {
            getSelectedItemNjump("~/report/jsdbjsPage.aspx");
        }       

        protected void btnCompareTAll_Click(object sender, EventArgs e)
        {
            getSelectedItemNjump("~/report/jsdbwzPage.aspx");
        }

        private void getSelectedItemNjump(string path)
        {
            ArrayList ar = new ArrayList();
            bool b = false;
            for (int i = 0; i < GridView1.Rows.Count; i++)
            {
                b = ((CheckBox)GridView1.Rows[i].FindControl("chkItem")).Checked;
                if (true == b)
                {
                    ar.Add(GridView1.Rows[i].Cells[1].Text);
                }
            }
            Session["selList"] = ar;
            Response.Redirect(path);
        }

        protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            GridView1.PageIndex = e.NewPageIndex;
            QueryPaper();
        }

        protected void ddlTGroup_SelectedIndexChanged(object sender, EventArgs e)
        {
            ddlTeacher.Items.Clear();
            Database objDB = new SqlDatabase(M_CONSTR);
            DataSet ds = objDB.ExecuteDataSet(CommandType.Text, "SELECT * FROM Table_TEACHER where groupid=" + ddlTGroup.SelectedValue);
            ddlTeacher.DataSource = ds.Tables[0];
            ddlTeacher.DataTextField = "TeacherName";
            ddlTeacher.DataValueField = "TeacherID";
            ddlTeacher.DataBind();
            ddlTeacher.Items.Insert(0, (new ListItem("--全部--", "0")));
            ddlTeacher.SelectedIndex = 0;
        }

        private void CreateExcel(DataSet ds, string FileName)
        {
            System.Web.HttpResponse resp;
            resp = Page.Response;
            resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            resp.AppendHeader("Content-type:application/vnd.ms-excel;charset=utf8", "attachment;filename=" + FileName);
            string colHeaders = "", ls_item = "";

            //定义表对象与行对象，同时用DataSet对其值进行初始化 
            DataTable dt = ds.Tables[0];
            DataRow[] myRow = dt.Select();//可以类似dt.Select("id>10")之形式达到数据筛选目的
            int i = 0;
            int cl = dt.Columns.Count;


            //取得数据表各列标题，各标题之间以/t分割，最后一个列标题后加回车符 
            for (i = 0; i < cl; i++)
            {
                if (i == (cl - 1))//最后一列，加/n
                {
                    colHeaders += dt.Columns[i].Caption.ToString() + "\n";
                }
                else
                {
                    colHeaders += dt.Columns[i].Caption.ToString() + "\t";
                }

            }
            resp.Write(colHeaders);
            //向HTTP输出流中写入取得的数据信息 

            //逐行处理数据   
            foreach (DataRow row in myRow)
            {
                //当前行数据写入HTTP输出流，并且置空ls_item以便下行数据     
                for (i = 0; i < cl; i++)
                {
                    if (i == (cl - 1))//最后一列，加/n
                    {
                        ls_item += row[i].ToString() + "\n";
                    }
                    else
                    {
                        ls_item += row[i].ToString() + "\t";
                    }

                }
                resp.Write(ls_item);
                ls_item = "";

            }
            resp.End();
        } 

        protected void btnExport_Click(object sender, EventArgs e)
        {
            try
            {
                string strDate = dtMonth.Value;
                DateTime dt = DateTime.Parse(strDate);
                int month = dt.Month;
                int year = dt.Year;

                Database objDB = new SqlDatabase(M_CONSTR);

                DataSet ds = objDB.ExecuteDataSet(CommandType.Text,

                    @"SELECT 
PaperID as 序号,  PaperName as 问卷名称,
(select BrandName from Table_BRAND where BrandID = p.BrandID) as 品牌,
(select RegionName from Table_REGION where RegionID = p.RegionID) as 区域,
(select PositionName from Table_POSITION where PositionID = p.PositionID) as 岗位,
(select TeacherName from Table_TEACHER where TeacherID = p.TeacherID) as 讲师,
(select PMNM from Table_PM where PMID = p.PMID) as 项目经理,
(select PTYPENM from Table_PTYPE where PTYPEID = p.PTYPEID) as 项目类型,
TrainingDays as 培训天数,
StudentCounts as 学员数量,
SubmitTimes as 问卷数量, 
'100' as 满意度总分,
(select cast((convert(decimal,sum(convert(int,qanswer)))*100/convert(decimal,(count(*) * 5))) as decimal(10,2))  from table_result where qid <18 and paperid = p.paperid) as 平均分
FROM Table_PAPER p where YEAR(p.CreateTime)=" + year.ToString()+ @"and MONTH(p.CreateTime)=" +month.ToString());

                if (ds != null && ds.Tables[0].Rows.Count > 0)
                {
                    CreateExcel(ds, year.ToString() + "年" + month.ToString() + "月" + Guid.NewGuid().ToString().Substring(0, 5) + ".xls");
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
            }            
        }
    }
}